const Service = require("egg").Service;

class BaseService extends Service {

  constructor(param){
    super(param)
    //主键
    this.pk = 'id'
    //表名重命名
    this.aliasStr = ''
    //连表查询
    this.joinStr = ''
    //查询的字段
    this.fieldStr = '*'
    //条件
    this.whereStr = ''
    //查询的数量
    this.limitStr = ''
    //更新或新增的键
    this.keyStr = ''
    //更新或新增的值
    this.valueStr = ''
    //更新组合
    this.updateStr = ''
    //排序
    this.orderStr = ''
    //分组
    this.groupStr = ''
    //sql语句
    this.sql = ''
  }
  
  /*
   * 打印sql语句
  */
  getLastSql(){
  	return this.sql
  }

  /*
   * 组装查询的sql语句
  */
  getSql(){
    this.sql = `select ${this.fieldStr} from ${this.tableName} ${this.aliasStr} ${this.joinStr} ${this.whereStr} ${this.orderStr} ${this.groupStr} ${this.limitStr}`
    return this.sql
  }
  
  /*
   * 组装新增的sql语句
  */
  getInsertSql(){
    this.sql = `insert into ${this.tableName} ${this.keyStr} values ${this.valueStr}`
  	return this.sql
  }
  
  /*
   * 组装更新的sql语句
  */
  getUpdateSql(){
    this.sql = `update ${this.tableName} set ${this.updateStr} ${this.whereStr}`
  	return this.sql
  }
  
  /*
   * 组装删除的sql语句
  */
  getDeleteSql(){
    this.sql = `delete from ${this.tableName} ${this.whereStr}`
  	return this.sql
  }
  
  /*
   * 组装连表查询sql语句
  */
  getJoinSql(){
    this.sql = `select ${this.fieldStr} from ${this.tableName} ${this.aliasStr} ${this.joinStr} ${this.whereStr} ${this.orderStr} ${this.limitStr}`
  	return this.sql
  }

  /*
   * 指定查询的表名和主键
   * 主键pk可不传，默认为id
  */
  Db(tableName,pk = 'id'){
    this.tableName = tableName
    this.pk = pk
    //表名重命名
    this.aliasStr = ''
    //连表查询
    this.joinStr = ''
    //查询的字段
      this.fieldStr = '*'
    //条件
    this.whereStr = ''
    //查询的数量
    this.limitStr = ''
    //更新或新增的键
    this.keyStr = ''
    //更新或新增的值
    this.valueStr = ''
    //更新组合
    this.updateStr = ''
    //排序
    this.orderStr = ''
    //分组
    this.groupStr = ''
    //sql语句
    this.sql = ''
    return this
  }

  /*
   * 查询条件
   * param为查询的字段
   * condition为条件，= > < in
   * value为查询的值
  */
  where(param,condition,value){
    if(condition == 'in'){
      let str = ''
      let arr = value.split(',')
      for(let o in arr){
        str += `,'${arr[o]}'`
      }
      str = str.substr(1)
      value = `(${str})`
    }else{
      value = `'${value}'`
    }
    if(this.whereStr == ''){
      this.whereStr += `where ${param} ${condition} ${value} `
    }else{
      this.whereStr += `and ${param} ${condition} ${value}`
    }
    return this
  }

  /*
   * 查询条件
   * param为查询的字段
   * condition为条件，= > < in like
   * value为查询的值
  */
  whereOr(param,condition,value){
    if(condition == 'in'){
      let str = ''
      let arr = value.split(',')
      for(let o in arr){
        str += `,'${arr[o]}'`
      }
      str = str.substr(1)
      value = `(${str})`
    }else{
      value = `'${value}'`
    }
	  this.whereStr += `or ${param} ${condition} ${value}`
	  return this
  }

  /*
   * 查询某个时间区间
   * time为时间字段
   * startTime开始时间
   * endTime结束时间
  */
  whereBetweenTime(time,startTime,endTime){
    // 判断前方是否已经使用了where
    if(this.whereStr.indexOf('where') > -1){
      this.whereStr += `${time} > '${startTime}' and ${time} < '${endTime}'`
    }else{
      this.whereStr += `where ${time} > '${startTime}' and ${time} < '${endTime}'`
    }
    return this
  }

  /*
   * 查询一条数据
   * 如果传入值，则根据主键查询一条数据
  */
  async find(id = ''){
    this.limitStr = 'limit 1'
    if(id != ''){
      this.whereStr = `where ${this.pk} = '${id}'`
    }
    let result = await this.app.mysql.query(this.getSql())
    return result[0]
  }

  /*
   * 查询多条数据
  */
  async select(){
	  return await this.app.mysql.query(this.getSql())
  }

  /*
   * 新增一条数据
   * 传入一个对象
  */
  async insert(obj){
	  for (let i in obj) {
      if(obj[i] == undefined){
        obj[i] = ''
      }
      this.keyStr += `,${i}`
      this.valueStr += `,'${obj[i]}'`
	  }
	  this.keyStr = `(${this.keyStr.substr(1)})`
	  this.valueStr = `(${this.valueStr.substr(1)})`
	  return await this.app.mysql.query(this.getInsertSql())
  }
  
  /*
   * 新增多条数据
   * 传入一个数组
   * 注意每个数组对象的结构需一致
  */
  async insertAll(array){
    for (let i = 0;i <= array.length-1; i++) {
      let keyStr = ''
      let valueStr = ''
      for(let o in array[i]){
        if(array[i][o] == undefined){
          array[i][o] = ''
        }
        keyStr += `,${o}`
        valueStr += `,'${array[i][o]}'`
      }
      keyStr = keyStr.substr(1)
      valueStr = valueStr.substr(1)
      this.keyStr = `(${keyStr})`
      this.valueStr += `,(${valueStr})`
    }
    this.valueStr = this.valueStr.substr(1)
    return await this.app.mysql.query(this.getInsertSql())
  }

  /*
   * 更新一条数据
   * 传入一个对象
  */
  async update(obj){
	  for (let i in obj) {
      this.updateStr += `,${i} = '${obj[i]}'`
	  }
	  this.updateStr = this.updateStr.substr(1)
	  return await this.app.mysql.query(this.getUpdateSql())
  }

  /*
   * 删除一条数据
  */
  async delete(){
	  return await this.app.mysql.query(this.getDeleteSql())
  }

  /*
   * 排序条件
   * field代表根据哪个字段排序
   * sort代表排序规则，desc倒序，asc正序
  */
  order(field,sort = 'desc'){
	  this.orderStr = `order by ${field} ${sort}`
	  return this
  }
  
  /*
   * 分页查询
   * current第几页
   * size每页显示多少条
  */
  page(current,size){
	  current = ((current - 1) * size - 1) <= -1 ? 0 : ((current - 1) * size - 1)
	  this.limitStr = `limit ${current},${size}`
	  return this
  }

  /*
   * 指定要查询的数量
  */
  limit(number){
	  this.limitStr = `limit ${number}`
	  return this
  }

  /*
   * 指定要显示的字段
   * 传入一个字符串 id,name,age
  */
  field(str){
	  this.fieldStr = str
	  return this
  }

  /*
   * 分组查询
  */
  group(str){
    this.groupStr = `group by ${str}`
    return this
  }

  /*
   * 查询数量
  */
  async count(){
    this.fieldStr = 'count(*) as count'
    this.limitStr = ''
    let result = await this.app.mysql.query(this.getSql())
    let count = 0
    if(result.length > 0){
      count = result[0].count
    }
    return count
  }
  
  /*
   * 用于设置当前数据表的别名
   * 便于连表查询
  */
  alias(str){
	  this.aliasStr = `as ${str}`
	  return this
  }
  
  /*
   * 关联查询
   * tableNameStr表名和别名 例: order B
   * whereStr关联条件 例: A.id = B.id
   * type关联类型,可以为inner,left,right,full,默认为left
   * inier: 如果表中有至少一个匹配，则返回行
   * left: 即使右表中没有匹配，也从左表返回所有的行
   * right: 即使左表中没有匹配，也从右表返回所有的行
   * full: 只要其中一个表中存在匹配，就返回行
  */
  join(tableNameStr,whereStr,type = 'left'){
	  this.joinStr += `${type} join ${tableNameStr} on ${whereStr} `
	  return this
  }

}

module.exports = BaseService;
